package com.verbetia.musictrends.persistence.hsqldb;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UsersPersMngr {
	
	private static String CONNECTION_STRING="jdbc:hsqldb:hsql://localhost:9001";

	static Connection conn;
	
	
	public static int USER_NOT_EXSIST = -1; 
	
	public UsersPersMngr()
	{
			try {
				Class.forName("org.hsqldb.jdbcDriver");
			} catch (ClassNotFoundException cnfe)
			{
				System.out.println("Error loading org.hsqldb.jdbcDriver " + cnfe);
			}
	   
			try {
						
	        conn = DriverManager.getConnection(UsersPersMngr.CONNECTION_STRING,        		
	                                           "sa",                     // username
	                                           "");                      // password
	        
			}
			catch (SQLException sqle)
			{
				System.out.println(sqle);
				
			}
					
	}
	public void createInstance(String username, String password, int lang)
		throws Exception
	{		
		System.out.println("TestDemobase\n");	
		
		Statement statement = null;
	    ResultSet resultSet = null;

	        statement = conn.createStatement();
	        String query  = "INSERT INTO users (id, username, password) VALUES( NULL, '"+ username +"','"+ password + "')";

	    	Statement st = null;

		     st = conn.createStatement();    // statements
		        
		        
		    int i = st.executeUpdate(query);    // run the query

		    if (i == -1) {
		            System.out.println("db error : " + query);
		    }

		    st.close();
					
	
	}
	
	
	public UserEntity getUserProfile(String userid)
		throws SQLException
	{
		ResultSet rs = null;
		String query = "SELECT id, username, password  FROM users WHERE id=" + userid ; 
		UserEntity u = new UserEntity();
		
		Statement st = null;

		
	    st = conn.createStatement();    // statements
	               
	    rs  = st.executeQuery(query);    // run the query

	    for (; rs.next(); ) {
	    
	    	u.setUserid(rs.getString(1));
	    	u.setUsername(rs.getString(2));
	    	u.setPassword(rs.getString(3));
	    	
	    }
    
	    st.close();



		
		return u;
		
	}
	
	
	public int authenticateUser(String username, String password)	
		throws SQLException
	{
		int userid=UsersPersMngr.USER_NOT_EXSIST;
		ResultSet rs = null;
		String query = "SELECT id FROM users WHERE username='"+ username + "' and password='" + password +"'" ; 
		System.out.println(query);
		Statement st = null;

		
	    st = conn.createStatement();    // statements
	               
	    rs  = st.executeQuery(query);    // run the query

	    if (rs==null)
	    	return UsersPersMngr.USER_NOT_EXSIST;
	    
	    for (; rs.next(); ) {
	    	
	    	userid = rs.getInt(1);
        }
    
	    st.close();

		return userid;
		
	}
	
  public String getUsername(String userid)	
	throws SQLException
{
	String username= null;
	ResultSet rs = null;
	String query = "SELECT username FROM users WHERE id=" + userid; 
	System.out.println(query);
	Statement st = null;

	
    st = conn.createStatement();    // statements
               
    rs  = st.executeQuery(query);    // run the query

    if (rs==null)
    	return "";
    
    for (; rs.next(); ) {
    	
    	username = rs.getString(1);
    }

    st.close();

	return username;
	
}

}
